const { exec } = require('./mysql');
const { formatSqlObj } = require('../libs/common');
const logger = require('../libs/log').logger;

//新增
const Add = function (tab_name, doc) {
    try {
        let { cols, vals, fmt } = formatSqlObj(doc);
        let sql = `INSERT INTO ${tab_name}  (${cols.join(',')}) VALUES (${fmt.join(',')}) `;
        let sqlParams = vals;
        logger.info('sql--,',sql)
        logger.info('sqlParams--,',JSON.stringify(sqlParams))
        return exec(sql, sqlParams).then(result => {
            if (result.insertId) {
                let sqladd = `SELECT * FROM ${tab_name} WHERE id =${result.insertId} limit 1`;
                return exec(sqladd).then(rt => {
                    return rt && rt.length ? rt[0] : null;
                })
            } else {
                return result;
            }
        })
    } catch (error) {
        logger.info('-----Add----', error)
        throw error
    }
}

//删除接口
const DelById = function (tab_name, id) {
    try {
        if (!id) {
            return;
        }
        const sql = `DELETE FROM ${tab_name} WHERE id = ${id}  `
        return exec(sql).then(result => {
            return result.affectedRows > 0
        })
    } catch (error) {
        logger.info('-----DelById----', error)
        throw error
    }
}
//删除接口
const DelMany = function (tab_name, doc) {
    try {
        let { cols, vals } = formatSqlObj(doc);

        let sql = `DELETE FROM ${tab_name} WHERE 1=1  `
        let sqlParams = null;
        if (cols.length) {
            sql += ` AND ${cols.join('=? AND ')}=? `;
            sqlParams = vals;
        }
        return exec(sql, sqlParams).then(result => {
            return result.affectedRows > 0
        })
    } catch (error) {
        logger.info('-----DelMany----', error)
        throw error
    }
}
//列表
const Query = function (tab_name, doc, select) {
    try {
        select = select ? select : '*';
        let sql = `SELECT ${select} FROM ${tab_name}   WHERE 1=1 `;
        let sqlParams = null;
        if (doc) {
            let { cols, vals, fmt } = formatSqlObj(doc);
            if (cols.length) {
                sql += ` AND ${cols.join('=? AND ')}=? `;
                sqlParams = vals;
            }
        }
        sql += ' ORDER BY updatetime DESC ';
        return exec(sql, sqlParams).then(rows => {
            return rows || []
        })
    } catch (error) {
        logger.info('-----Query----', error)
        throw error
    }
}

//列表-带分页
//select * from table where id > (select id from table order by id limit m, 1) limit n;
const QueryList_Page = function (tab_name, doc, select, pagenum, pagesize, fcode) {
    try {
        select = select || '*';
        pagenum = pagenum || 1;
        pagesize = pagesize || 5;
        let sql = `SELECT ${select} FROM ${tab_name}  WHERE 1=1 `;
        let sqlParams = null;
        if (tab_name == 'hzz_peoples') {
            sql += ' AND avator is not null  AND isopen<>1 '
        }
        if (doc) {
            let { cols, vals, fmt } = formatSqlObj(doc);
            if (cols.length) {
                sql += ` AND ${cols.join('=? AND ')}=? `;
                sqlParams = vals;
            }
        }
        // sql += ` AND id >= (SELECT id FROM ${tab_name} ORDER BY id LIMIT ${(pagenum-1)*pagesize},1) LIMIT ${pagesize} `;
        if (fcode) {
            sql += ` AND CODE NOT IN ('${fcode}')`;
        }
        sql += ' ORDER BY id ASC ,updatetime DESC  ';
        sql += ` LIMIT ${(pagenum - 1) * pagesize}, ${pagesize}; `
        return exec(sql, sqlParams).then(rows => {
            return rows || []
        })
    } catch (error) {
        logger.info('-----QueryList_Page----', error)
        throw error
    }
}

// 获取总数
const Exec_Count = function (tab_name, doc, fcode) {
    try {
        let sql = `SELECT count(1) as total FROM ${tab_name}   WHERE 1=1 `;
        let sqlParams = null;
        if (doc) {
            let { cols, vals, fmt } = formatSqlObj(doc);
            if (cols.length) {
                sql += ` AND ${cols.join('=? AND ')}=? `;
                sqlParams = vals;
            }
        }
        if (fcode) {
            sql += ` AND CODE NOT IN ('${fcode}')`;
        }
        return exec(sql, sqlParams).then(result => {
            return result && result.length ? result[0].total : 0;
        })
    } catch (error) {
        logger.info('-----Exec_Count----', error)
        throw error
    }
}
// 获取总数
const Execsql_Count = function (sql) {
    try {
        return exec(sql).then(result => {
            return result && result.length ? result[0].total : 0;
        })
    } catch (error) {
        logger.info('-----Exec_Count----', error)
        throw error
    }
}

//修改
const Update = function (tab_name, doc) {
    try {
        let { cols, vals, fmt, id } = formatSqlObj(doc);
        let sql = `UPDATE ${tab_name} SET ${cols.join('=?,')}=? WHERE id=? `;
        let sqlParams = vals.concat([id]);
        return exec(sql, sqlParams).then(result => {
            return result.affectedRows > 0
        })
    } catch (error) {
        logger.info('-----Update----', error)
        throw error
    }
}

//查询详情
const QueryById = function (tab_name, id) {
    try {
        let sql = `SELECT * FROM ${tab_name} where id=? limit 1`;
        let sqlParams = [id];
        return exec(sql, sqlParams).then(result => {
            return result && result.length ? result[0] : null;
        })
    } catch (error) {
        logger.info('-----QueryById----', error)
        throw error
    }
}
// 执行sql
const Exec = function (sql) {
    try {
        return exec(sql).then(result => {
            return result;
        })
    } catch (error) {
        logger.info('-----Exec----', error)
        throw error
    }
}
// 执行sql
const ExecGetOne = function (sql) {
    try {
        return exec(sql).then(rows => {
            return rows && rows.length ? rows[0] : null
        })
    } catch (error) {
        logger.info('-----Exec----', error)
        throw error
    }
}
// 执行sql
const ExecSqlparmater = function (sql, sqlParams) {
    try {
        return exec(sql, sqlParams).then(result => {
            return result;
        })
    } catch (error) {
        logger.info('-----ExecSqlparmater----', error)
        throw error
    }
}
// 查询一条
const QueryOne = function (tab_name, doc, select) {
    try {
        select = select ? select : '*';
        let sql = `SELECT ${select} FROM ${tab_name}   WHERE 1=1 `;
        let sqlParams = null;
        if (doc) {
            let { cols, vals, fmt } = formatSqlObj(doc);
            if (cols.length) {
                sql += ` AND ${cols.join('=? AND ')}=? `;
                sqlParams = vals;
            }
        }
        return exec(sql, sqlParams).then(rows => {
            return rows && rows.length ? rows[0] : null
        })
    } catch (error) {
        logger.info('-----QueryOneerror------', error)
        throw error;
    }
}

module.exports = {
    Add,
    DelById,
    DelMany,
    Query,
    Update,
    QueryById,
    Exec,
    ExecSqlparmater,
    QueryOne,
    Exec_Count,
    Execsql_Count,
    QueryList_Page,
    ExecGetOne
}